﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
//using System.Windows.Forms;


namespace FuncSystem
{
    public class Data
    {

        public static void combine()
        {
            DataTable MyTable = new DataTable();
            MySql.Data.MySqlClient.MySqlConnection conn;
            MySql.Data.MySqlClient.MySqlCommand cmd;
            MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;

            conn = new MySql.Data.MySqlClient.MySqlConnection();
            cmd = new MySql.Data.MySqlClient.MySqlCommand();
            myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

            conn.ConnectionString = "server=92.255.251.110;uid=ast;pwd=7hxSAYcrYCjn7nb5;database=pager;";
            //conn.ConnectionString = "server=localhost;uid=ast;pwd=7hxSAYcrYCjn7nb5;database=pager;";
            cmd.Connection = conn;
            conn.Open();

            cmd.CommandText = "SELECT *	from `history_quik_temp` where  not exists(select * from `history_quik` where `history_quik`.`date` = `history_quik_temp`.`date`"+
                                                " and `history_quik`.`time` = `history_quik_temp`.`time` and `history_quik`.`seccode_trans` = `history_quik_temp`.`seccode_trans`" +
												" and `history_quik`.`per` = `history_quik_temp`.`per`)" +
                                         "GROUP BY `history_quik_temp`.`date`, `history_quik_temp`.`time`,`history_quik_temp`.`seccode_trans`, `history_quik_temp`.`open`, `history_quik_temp`.`high`," +												
												"`history_quik_temp`.`low`,`history_quik_temp`.`close`,`history_quik_temp`.`vol`";

            myAdapter.SelectCommand = cmd;
            myAdapter.Fill(MyTable);


            foreach (DataRow row in MyTable.Rows)
            {
                cmd.CommandText = "INSERT INTO `history_quik` (`seccode`,`seccode_trans`,`per`,`date`,`time`,`open`,`high`,`low`,`close`,`vol`,`macd`,`macdg`,`macds`, `elder`, `tenkan`, `kijun`, `senkou_span1`, `senkou_span2`,`chinkou`) "
                                            + "VALUES ('"
                                            + Convert.ToString(row["seccode"]) + "','"
                                            + Convert.ToString(row["seccode_trans"]) + "','"
                                            + Convert.ToString(row["per"]) + "','"
                                            + Convert.ToString(row["date"]) + "','"
                                            + Convert.ToString(row["time"]) + "','"
                                            + Convert.ToString(row["open"]).Replace(',', '.') + "','"
                                            //+ Convert.ToString(MaxBuyVol).Replace(',', '.') + "','"
                                            + Convert.ToString(row["high"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["low"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["close"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["vol"]) + "','"
                                            + Convert.ToString(row["macd"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["macdg"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["macds"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["elder"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["tenkan"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["kijun"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["senkou_span1"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["senkou_span2"]).Replace(',', '.') + "','"
                                            + Convert.ToString(row["chinkou"]).Replace(',', '.')
                                            + "')";
                cmd.ExecuteNonQuery();
            }


            ///////////////
            //MessageBox.Show("OK");//mscandle.OpenPrice - цена открытия свечки 
            //return MyTable;
        }
        public static void toBDSignal(DataTable table)
        {
            //подключаем MySQL

            string flag = "";
            foreach (DataRow row in table.Rows)
            {
                flag = Convert.ToString(row["seccode"]);
            }

            if (flag != "")
            {
                MySql.Data.MySqlClient.MySqlConnection conn;
                MySql.Data.MySqlClient.MySqlCommand cmd;
                conn = new MySql.Data.MySqlClient.MySqlConnection();
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.ConnectionString = "server=92.255.251.110;uid=ast;pwd=7hxSAYcrYCjn7nb5;database=pager;";
                //conn.ConnectionString = "server=localhost;uid=ast;pwd=7hxSAYcrYCjn7nb5;database=pager;";
                cmd.Connection = conn;
                conn.Open();

                foreach (DataRow row in table.Rows)
                {
                    cmd.CommandText = "INSERT INTO `signal_temp` (`seccode`,`date`,`time`,`signal`,`short_long`,`forces`,`price`,`comments`,`time_frame`,`sent`,`type`) "
                                                + "VALUES ('"
                                                + Convert.ToString(row["seccode"]) + "','"
                                                + Convert.ToString(row["date"]) + "','"
                                                + Convert.ToString(row["time"]) + "','"
                                                + Convert.ToString(row["name"]) + "','"
                                                + Convert.ToString(row["short_long"]) + "','"
                                                + Convert.ToString(row["forces"]).Replace(',', '.') + "','"
                                                + Convert.ToString(row["price"]).Replace(',', '.') + "','"
                                                + Convert.ToString(row["comments"]) + "','"
                                                + Convert.ToString(row["tf"]) + "','"
                                                + "0" + "','"
                                                + "div"
                                                + "')";
                    cmd.ExecuteNonQuery();
                }
            }

        }
        public static void toCSV(DataTable table, string filename, int header, string seperateChar)
        {
            System.IO.StreamWriter sr = null;
            try
            {

                sr = new System.IO.StreamWriter(filename);
                string seperator = "";
                System.Text.StringBuilder builder = new System.Text.StringBuilder();
                if (header == 1)
                {
                    foreach (DataColumn col in table.Columns)
                    {
                        builder.Append(seperator).Append(col.ColumnName);
                        seperator = seperateChar;
                    }
                    sr.WriteLine(builder.ToString());
                }
                foreach (DataRow row in table.Rows)
                {
                    seperator = "";
                    builder = new System.Text.StringBuilder();
                    foreach (DataColumn col in table.Columns)
                    {
                        builder.Append(seperator).Append(row[col.ColumnName]);
                        seperator = seperateChar;
                    }
                    sr.WriteLine(builder.ToString());
                }
            }
            finally
            {
                if (sr != null)
                {
                    sr.Close();
                }
            }
        }



        public static DateTime toSystemTime(string Mydate, string MyTime)
        {
            //DateTime  sysTime = new DateTime();
            int year = System.Convert.ToInt32(Mydate.Substring(0, 4));
            int month = System.Convert.ToInt32(Mydate.Substring(4, 2));
            int day = System.Convert.ToInt32(Mydate.Substring(6, 2));
            int hour = System.Convert.ToInt32(MyTime.Substring(0, 2));
            int min = System.Convert.ToInt32(MyTime.Substring(2, 2));
            DateTime sysTime = new DateTime(year, month, day, hour, min, 0);
            //sysTime = (sysTime.AddMinutes(-30)); //пример вычесть 30 минут
            return sysTime;
        }

        public static double GetTable(DataTable Candles, string column, int id)
        {
            DataRow row2 = Candles.Rows.Find(id);
            return System.Convert.ToDouble(row2[column]);
        }

        public static string GetField(DataTable Candles, string column, int id)
        {
            DataRow row2 = Candles.Rows.Find(id);
            return System.Convert.ToString(row2[column]);
        }
        
        
    }
}
